#!/usr/bin/env python
# encoding: utf-8
'''
@author: 迟乐飞 
@contact: lefei_chi@foxmail.com
@software: pycharm
@file: rent_day.py
@time: 2019-11-14 14:03
@desc: 更新日租日价格
'''
import os, sys

pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)
from report_system.utils.db_util import con_mysql_cityhome, con_mysql_report, con_mysql_report_test, con_mysql_tpp, \
    con_mysql_youjia
# from utils.db_util import con_mysql_report, con_mysql_tpp, con_mysql_youjia
import logging

import datetime
from dateutil.relativedelta import relativedelta
import pandas as pd


class RentDay(object):
    """
    日租金相关数据：
    1.租金
    2.暖气
    3.物业
    4.网费
    有家的部分房源没有租金，需要计算
    有家那边没租金的取城市的所有同居室房屋的均值 by 章宪

    城宿房屋对应
    """

    def cs_price(self):
        # 城宿日租价格
        price_sql = """
        SELECT
	city.short_name 城市,
	cchb.*,
	lh.id as house_id
FROM
	youjia.lod_house lh
	LEFT JOIN youjia.city ON city.id = lh.city_id
	LEFT JOIN youjia.lod_house_online_time lhot ON lhot.lod_house_id = lh.id -- and lhot.sales_channel_id = 4
	LEFT JOIN youjia.lod_house_compact lhc ON lhc.house_id = lh.id 
	AND lhc.is_delete = 0
	LEFT JOIN youjia.cs_compact_trust cct ON cct.id = lhc.compact_trust_id 
	AND cct.is_delete = 0
	LEFT JOIN youjia.cs_compact_house cch ON cch.id = cct.compact_house_id
	LEFT JOIN youjia.cs_compact_house_bill cchb ON cchb.compact_house_id = cct.compact_house_id 
	AND cchb.is_delete = 0 
WHERE
	lh.own_type = 2 
	AND lh.city_id <> 376 

        """

        contents = con_mysql_youjia.find_all_as_dict_low(price_sql)
        n = 0
        for content in contents:
            n += 1
            house_id = content['house_id']
            pay_cycle_start_date = content['pay_cycle_start_date']
            pay_cycle_end_date = content['pay_cycle_end_date']
            month_price = content['month_price']
            # 暂不进入
            print(house_id, n, len(contents), datetime.datetime.now())
            # agent_price = content['agent_price']
            # maintain_fund = content['agent_price']

            month_list = self.get_months_list(pay_cycle_start_date=pay_cycle_start_date,
                                              pay_cycle_end_date=pay_cycle_end_date)

            for month in month_list:
                day_price = round(month_price / ((month[1] - month[0]).days))
                if month[0] > (datetime.datetime.now().date() + relativedelta(months=+1)) or month[0] < (
                        datetime.datetime.now().date() + relativedelta(months=- 1)):
                    continue
                self.update_rent_day(dt1=month[0], dt2=month[1], price=day_price, house_id=house_id)
                a = 1

        pass

    def get_months_list(self, pay_cycle_start_date, pay_cycle_end_date):
        months_list = [((pay_cycle_start_date + relativedelta(months=+(i))),
                        (pay_cycle_start_date + relativedelta(months=+(i + 1)) + relativedelta(days=-1))) for i in
                       range((pay_cycle_end_date - pay_cycle_start_date).days // 30)]

        return months_list

    def youjia_price(self):
        # 有家日租价格
        price_sql = """
        SELECT
ce.*
	FROM
		invest_compact_house ce
		LEFT JOIN invest_compact_estimate inv ON ce.house_id = inv.house_id
		LEFT JOIN city c ON ce.`city_id` = c.`id`
		LEFT JOIN users u ON ce.invest_id = u.id
		LEFT JOIN lod_house lo ON ce.house_id = lo.id 
	WHERE
	ce.`state` = 10 
	AND ce.`city_id` <> 376

	order by  rent_grow_ratio
        """

        contents = con_mysql_youjia.find_all_as_dict_low(price_sql)
        n = 0
        for content in contents:
            n += 1
            house_id = content['house_id']
            pay_cycle_start_date = content['lease_date_begin']
            pay_cycle_end_date = content['lease_date_end']
            month_price = content['rent_grow_ratio']
            # 暂不进入
            a = 1

            years = (pay_cycle_end_date - pay_cycle_start_date).days // 365
            # 每年的月租金
            pre_year_rent_price = list(map(float, month_price.split(',')[:years]))
            # 每年的租期范围
            pre_year_range = [((pay_cycle_start_date + relativedelta(years=+(i))),
                               (pay_cycle_start_date + relativedelta(years=+(i + 1)) + relativedelta(days=-1))) for i in
                              range(years)]
            # 每年租期范围于租金构成dict
            pre_year_dict = dict(zip(pre_year_range, pre_year_rent_price))
            # 物业费
            property_fee = content['property_fee']
            # 取暖费
            heating_fee = content['heating_fee']
            # 网费
            net_fee = content['net_fee']
            for k, v in pre_year_dict.items():
                day_num = (k[1] - k[0]).days
                # 物业费/取暖费/网费 日价格
                property_fee_day, heating_fee_day, net_fee_day = round(property_fee / day_num), round(
                    heating_fee / day_num), round(net_fee / day_num)

                month_list = self.get_months_list(pay_cycle_start_date=k[0],
                                                  pay_cycle_end_date=k[1])

                for month in month_list:
                    day_price = round(
                        v * 100 / ((month[1] - month[0]).days)) + property_fee_day + heating_fee_day + net_fee_day
                    if month[0] > (datetime.datetime.now().date() + relativedelta(months=+ 1)) or month[0] < (
                            datetime.datetime.now().date() + relativedelta(months=- 1)):
                        continue
                    self.update_rent_day(dt1=month[0], dt2=month[1], price=day_price, house_id=house_id)
                pass
            print(house_id, n, len(contents), datetime.datetime.now())

        pass

    def update_rent_day(self, dt1: str, dt2: str, house_id: int, price: int):
        sql = "update wh_house_calendar set rent_day = {price} where house_id = '{house_id}' and dt between '{dt1}' and '{dt2}'".format(
            price=price, dt1=dt1, dt2=dt2, house_id=house_id
        )
        print(sql)
        con_mysql_report.insert(sql)
        pass

    def run(self):
        self.youjia_price()
        self.cs_price()
        self.brush_status_youjia()
        self.check_out_order()
        self.flush_wrong_rent()

    def brush_status_youjia(self):
        # 刷单信息
        #         sql = """
        #         select * from youjia.orders bo
        # left join youjia.order_detail od on od.order_id = bo.id
        # left join youjia.lod_house lh on lh.id = od.house_id
        # where bo.brush_status = 1
        # and bo.cancel_status = 0
        # and bo.pay_state = 1
        # and lh.city_id <> 376
        # and bo.actcheckout_day between '{}' and '{}'
        #         """.format(str(datetime.datetime.now().date() + relativedelta(months=- 1)), str(
        #             datetime.datetime.now().date() + relativedelta(months=+ 1)))
        sql = """
                select * from youjia.orders bo
        left join youjia.order_detail od on od.order_id = bo.id
        left join youjia.lod_house lh on lh.id = od.house_id
        where bo.brush_status = 1
        and bo.cancel_status = 0
        and bo.pay_state = 1
        and lh.city_id <> 376
        and bo.actcheckout_day BETWEEN CURRENT_DATE - 30 and CURRENT_DATE + 30
        
                """

        contents = con_mysql_youjia.find_all_as_dict_low(sql)
        for content in contents:
            house_id = content['house_id']
            actcheckin_day = content['actcheckin_day']
            actcheckout_day = content['actcheckout_day']
            brush_order_id = content['order_id']
            real_income = content['real_income']
            day_count = (actcheckout_day - actcheckin_day).days
            day_list = [datetime.datetime.strftime(x, '%Y-%m-%d') for x in
                        list(pd.date_range(start=actcheckin_day, end=actcheckout_day))][:-1]
            order_channel = content['order_channel']
            for dt in day_list:
                price = round(real_income / day_count)
                sql = "update wh_house_calendar set brush_income = {price},brush_night=1,brush_channel={brush_channel},brush_order_id={brush_order_id} where house_id = '{house_id}' and dt= '{dt}'".format(
                    price=price, dt=dt, house_id=house_id, brush_channel=order_channel, brush_order_id=brush_order_id
                )
                logging.info(sql)
                con_mysql_report.insert(sql)

    def check_out_order(self):
        # 离店订单信息
        sql = """
select 
od.house_id,
concat(bo.id,'') order_id,

date(bo.actcheckout_day) checkout_day, 
bo.order_channel 

from youjia.order_detail od 
left join youjia.orders bo on bo.id = od.order_id
left join youjia.orders_distribute ods on bo.id = ods.order_id
where bo.brush_status <> 1
AND (bo.pay_state = 1  or (bo.pay_state = 0 and  bo.order_channel=5))
AND bo.confirm_status = 1 
AND bo.cancel_status in (0,4,5)
and bo.actcheckin_day <> bo.actcheckout_day
and bo.city_id <> 376
and bo.special_status <> 3 
and date(bo.actcheckout_day) BETWEEN CURRENT_DATE - 30 and CURRENT_DATE + 30
        """
        contents = con_mysql_youjia.find_all_as_dict_low(sql)
        for content in contents:
            house_id = content['house_id']
            dt = content['checkout_day']
            checkout_channel = content['order_channel']
            checkout_order_id = content['order_id']
            sql = "update wh_house_calendar  set  checkout_order_id = {checkout_order_id},checkout_orders=1,checkout_channel = {checkout_channel} WHERE dt = '{dt}' and house_id ={house_id}".format(
                dt=dt, checkout_channel=checkout_channel, checkout_order_id=checkout_order_id, house_id=house_id
            )
            logging.info(sql)
            con_mysql_report.insert(sql)

    def tag_data(self):
        """
        目标数据
        :return:
        """
        contents = pd.read_excel(io='11月城市目标.xlsx').to_dict(orient='index')
        for content in contents.values():
            city = content['城市']
            target_check_rate = int(round(content['目标可售入住率'] * 10000, 0))
            target_adr = int(round(content['目标ADR'] * 100, 0))
            target_gmv = int(round(content['GMV'] * 100, 0))
            target_revpar = int(round(content['RevPar'] * 100, 0))
            target_rent = int(round(content['目标房租完成比'] * 10000))

            sql_find_city_house_id = "SELECT house_id FROM wh_house_static where city_name = '{}'".format(city)

            house_ids = con_mysql_report.find_all_as_dict_low(sql_find_city_house_id)
            for house_id in house_ids:
                update_sql = """
                update wh_house_calendar set  target_check_rate = {target_check_rate} ,target_adr={target_adr} ,
                target_gmv = {target_gmv},target_revpar={target_revpar},target_rent = {target_rent}
                where house_id = {house_id}
                and dt between '{dt1}' and '{dt2}'
                """.format(target_check_rate=target_check_rate, target_gmv=target_gmv, target_adr=target_adr,
                           target_revpar=target_revpar, target_rent=target_rent, house_id=house_id['house_id'],
                           dt1='2019-11-01', dt2='2019-11-30')
                con_mysql_report.insert(update_sql)
                print(update_sql)

    def flush_wrong_rent(self):
        """
        跟新 日租金为0的房源
        :return:
        """
        sql = """
SELECT
	whc.house_id,
	whc.dt,
	whc.rent_day,
	whs.title,
	whs.city_id,
	whs.room_num 
FROM
	youjia_report.wh_house_calendar whc
	LEFT JOIN  youjia_report.wh_house_static whs ON whs.house_id = whc.house_id 
WHERE
	whc.dt BETWEEN '2018-01-01' and '2021-01-01'
	AND whc.rent_day < 100 and whs.house_state >= 100 
        """

        contents = con_mysql_youjia.find_all_as_dict_low(sql)
        for content in contents:
            house_id = content['house_id']
            dt = content['dt']
            city_id = content['city_id']
            room_num = content['room_num']
            rent_day_sql = """
            SELECT
	ROUND(AVG(whc.rent_day),0) rent_day
FROM
	youjia_report.wh_house_calendar whc
	LEFT JOIN  youjia_report.wh_house_static whs ON whs.house_id = whc.house_id 
WHERE
	whc.dt ='{dt}'
	AND whc.rent_day > 100 and whs.house_state >= 100 
	and whs.city_id ={city_id} and whs.room_num = {room_num}
            """.format(dt=dt, city_id=city_id, room_num=room_num)
            try:
                rent_day = int(con_mysql_report.find_all_as_dict_low(rent_day_sql)[0]['rent_day'])

                update_rent_day_sql = "update wh_house_calendar set rent_day ={rent_day} where house_id = {house_id} and dt = '{dt}'".format(
                    house_id=house_id, rent_day=rent_day, dt=dt
                )
                logging.info(update_rent_day_sql)
                con_mysql_report.insert(update_rent_day_sql)
            except Exception as e:
                logging.error(e)


if __name__ == '__main__':
    RentDay().run()
    # RentDay().flush_wrong_rent()
